from flask import  Blueprint, request, session, jsonify
import MySQLdb.cursors
import datetime  # 添加这一行

from extensions import mysql  # 从 extensions 导入 mysql 实例

login_app = Blueprint('login', __name__)

# 登录页面路由
@login_app.route('/login', methods=['POST'])
def login():
    data = request.json
    username = data.get('username')
    password = data.get('password')

    # 通过数据库查询用户名和密码
    cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
    cursor.execute('SELECT * FROM user WHERE username = %s AND password = %s', (username, password))
    account = cursor.fetchone()

    # 如果查询到该用户，存储 session 信息并返回成功
    if account:
        session['loggedin'] = True
        session['id'] = account['id']
        session['username'] = account['username']
        session['role'] = account['role']  # 存储角色信息

        return jsonify({
            'code': '200',
            'message': '登录成功',
            'username': account['username'],
            'password': account['password'],
            'id': account['id'],
            'role': account['role']  # 返回用户角色给前端
        })
    else:
        return jsonify({
            'code': '500',
            'message': '用户名或者密码错误'
        })


@login_app.route('/update', methods=['POST'])
def update_user():
    data = request.json  # 获取前端传递的 JSON 数据
    new_username = data.get('username')
    new_password = data.get('password')
    user_id = session['id']  # 获取当前登录用户的 ID

    if not new_username or not new_password:
        return jsonify({
            'code': '400',
            'message': '用户名和密码不能为空'
        })
    print(user_id)
    try:
        # 更新用户账号和密码
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('UPDATE user SET username = %s, password = %s WHERE id = %s',
                       (new_username, new_password, user_id))
        mysql.connection.commit()

        # 更新 session 中的用户名
        session['username'] = new_username

        return jsonify({
            'code': '200',
            'message': '用户信息更新成功',
            'username': new_username,
            'password': new_password
        })

    except Exception as e:
        return jsonify({
            'code': '500',
            'message': '用户信息更新失败',
            'error': str(e)
        }), 500

# 登出路由
@login_app.route('/logout')
def logout():
    session.pop('loggedin', None)
    session.pop('id', None)
    session.pop('username', None)
    session.pop('role', None)
    return jsonify({
        'status': 'success',
        'message': 'You have been logged out.',
        'code':200
    })

@login_app.route('/users', methods=['GET'])
def get_all_users():
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM user')
        users = cursor.fetchall()  # 获取所有用户数据
        return jsonify(users), 200
    except Exception as e:
        return jsonify({'message': '获取用户失败', 'error': str(e)}), 500


# 根据 ID 获取用户
@login_app.route('/user/<int:user_id>', methods=['GET'])
def get_user(user_id):
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM user WHERE id = %s', (user_id,))
        user = cursor.fetchone()
        if user:
            return jsonify(user), 200
        return jsonify({'message': '用户不存在'}), 404
    except Exception as e:
        return jsonify({'message': '获取用户失败', 'error': str(e)}), 500

# 添加新用户
@login_app.route('/add', methods=['POST'])
def add_user():
    try:
        data = request.json or {}
        username = data.get('username')
        password = data.get('password')
        role = data.get('role')


        if not all([username, password, role]):
            return jsonify({'message': '缺少必要参数'}), 400


        with mysql.connection.cursor() as cursor:
            # Insert new user into the database
            cursor.execute("""
                INSERT INTO user (username, password, role)
                VALUES (%s, %s, %s)
            """, (username, password, role))

            mysql.connection.commit()

        return jsonify({'message': '用户创建成功', 'code': 200}), 200

    except Exception as e:
        return jsonify({'message': '创建用户失败', 'error': str(e), 'code': 500}), 500



# 更新用户信息
# 删除这个更新用户的函数
@login_app.route('/updateUser', methods=['PUT'])
def update_users():
    data = request.json  # 获取前端传递的 JSON 数据
    new_username = data.get('username')
    new_password = data.get('password')
    role = data.get('role')
    user_id =data.get('id')

    if not new_username or not new_password:
        return jsonify({
            'code': '400',
            'message': '用户名和密码不能为空'
        })

    try:
        # 更新用户账号和密码
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('UPDATE user SET username = %s, password = %s, role = %s WHERE id = %s',
                       (new_username, new_password, role, user_id))
        mysql.connection.commit()

        # 更新 session 中的用户名
        session['username'] = new_username

        return jsonify({
            'code': '200',
            'message': '用户信息更新成功',
            'username': new_username,
            'password': new_password,
             'role': role,
            'id': user_id
        })

    except Exception as e:
        return jsonify({
            'code': '500',
            'message': '用户信息更新失败',
            'error': str(e)
        })


# 删除用户
@login_app.route('/deleUser/<int:user_id>', methods=['DELETE'])
def delete_user(user_id):
    try:
        cursor = mysql.connection.cursor()
        cursor.execute('DELETE FROM user WHERE id = %s', (user_id,))
        mysql.connection.commit()

        return jsonify({'message': '用户删除成功'}), 200
    except Exception as e:
        return jsonify({'message': '删除用户失败', 'error': str(e)}), 500


# ============================
# 获取所有交易
# ============================
@login_app.route('/transactions', methods=['GET'])
def get_transactions():
    """
    获取 transaction 记录，可以根据查询参数进行条件查询
    支持按 user_id 和 transaction_type 查询（可扩展其他条件）
    示例请求：/transactions?user_id=USER_123&transaction_type=Online
    """
    try:
        # 获取查询参数
        user_id = request.args.get('user_id')
        transaction_type = request.args.get('transaction_type')
        device_type = request.args.get('device_type')



        # 构造基础 SQL 查询和参数列表
        query = "SELECT * FROM transactions"
        params = []
        conditions = []

        if user_id:
            conditions.append("user_id = %s")
            params.append(user_id)
        if transaction_type:
            conditions.append("transaction_type = %s")
            params.append(transaction_type)
        if device_type:
            conditions.append("device_type = %s")
            params.append(device_type)

        # 如果存在查询条件，则拼接 WHERE 子句
        if conditions:
            query += " WHERE " + " AND ".join(conditions)

        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute(query, params)
        rows = cursor.fetchall()
        cursor.close()
        return jsonify(rows), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500
# ============================
# 获取单条交易
# ============================
@login_app.route('/transactions/<int:transaction_id>', methods=['GET'])
def get_transaction(transaction_id):
    """
    根据 transaction_id 获取单条记录
    """
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute("SELECT * FROM transactions WHERE transaction_id = %s", (transaction_id,))
        row = cursor.fetchone()
        cursor.close()
        if row:
            return jsonify(row), 200
        else:
            return jsonify({"message": "Transaction not found"}), 404
    except Exception as e:
        return jsonify({"error": str(e)}), 500


# ============================
# 创建新交易
# ============================
@login_app.route('/transactions', methods=['POST'])
def create_transaction():
    """
    创建一条新的交易记录
    """
    data = request.json
    if not data:
        return jsonify({"error": "No JSON data provided"}), 400

    # 从请求体获取字段
    user_id = data.get("user_id")
    transaction_amount = data.get("transaction_amount")
    transaction_id = data.get("transaction_id")
    transaction_type = data.get("transaction_type")
    account_balance = data.get("account_balance")
    device_type = data.get("device_type")
    location = data.get("location")
    merchant_category = data.get("merchant_category")
    ip_address_flag = data.get("ip_address_flag", 0)
    previous_fraudulent_activity = data.get("previous_fraudulent_activity", 0)
    daily_transaction_count = data.get("daily_transaction_count", 0)
    avg_transaction_amount_7d = data.get("avg_transaction_amount_7d", 0)
    failed_transaction_count_7d = data.get("failed_transaction_count_7d", 0)
    card_type = data.get("card_type")
    card_age = data.get("card_age", 0)
    transaction_distance = data.get("transaction_distance", 0)
    authentication_method = data.get("authentication_method")
    risk_score = data.get("risk_score", 0)
    is_weekend = data.get("is_weekend", 0)
    fraud_label = data.get("fraud_label", 2)

    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        sql = """
        INSERT INTO transactions(
            transaction_id, user_id, transaction_amount, transaction_type,
            account_balance, device_type, location, merchant_category,
            ip_address_flag, previous_fraudulent_activity, daily_transaction_count,
            avg_transaction_amount_7d, failed_transaction_count_7d, card_type,
            card_age, transaction_distance, authentication_method,
            risk_score, is_weekend, fraud_label
        )
        VALUES (
            %s, %s, %s, %s,
            %s, %s, %s, %s,
            %s, %s, %s,
            %s, %s, %s,
            %s, %s, %s,
            %s, %s, %s
        )
        """
        cursor.execute(sql, (
            transaction_id,user_id, transaction_amount, transaction_type,
            account_balance, device_type, location, merchant_category,
            ip_address_flag, previous_fraudulent_activity, daily_transaction_count,
            avg_transaction_amount_7d, failed_transaction_count_7d, card_type,
            card_age, transaction_distance, authentication_method,
            risk_score, is_weekend, fraud_label
        ))
        mysql.connection.commit()
        new_id = cursor.lastrowid
        cursor.close()
        return jsonify({"message": "Created successfully", "transaction_id": new_id,'status':200}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500


# ============================
# 更新交易
# ============================
@login_app.route('/transactions/<string:transaction_id>', methods=['PUT'])
def update_transaction(transaction_id):
    """
    根据 transaction_id 更新交易
    """
    data = request.json
    if not data:
        return jsonify({"error": "No JSON data provided"}), 400

    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        sql = """
        UPDATE transactions
        SET
            user_id = %s,
            transaction_amount = %s,
            transaction_type = %s,
            account_balance = %s,
            device_type = %s,
            location = %s,
            merchant_category = %s,
            ip_address_flag = %s,
            previous_fraudulent_activity = %s,
            daily_transaction_count = %s,
            avg_transaction_amount_7d = %s,
            failed_transaction_count_7d = %s,
            card_type = %s,
            card_age = %s,
            transaction_distance = %s,
            authentication_method = %s,
            risk_score = %s,
            is_weekend = %s,
            fraud_label = %s
        WHERE transaction_id = %s
        """
        cursor.execute(sql, (
            data.get("user_id"),
            data.get("transaction_amount"),
            data.get("transaction_type"),
            data.get("account_balance"),
            data.get("device_type"),
            data.get("location"),
            data.get("merchant_category"),
            data.get("ip_address_flag"),
            data.get("previous_fraudulent_activity"),
            data.get("daily_transaction_count"),
            data.get("avg_transaction_amount_7d"),
            data.get("failed_transaction_count_7d"),
            data.get("card_type"),
            data.get("card_age"),
            data.get("transaction_distance"),
            data.get("authentication_method"),
            data.get("risk_score"),
            data.get("is_weekend"),
            data.get("fraud_label"),
            transaction_id
        ))
        mysql.connection.commit()
        cursor.close()

        # 更新数据库
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)

        cursor.execute('UPDATE transactions SET fraud_label = %s WHERE transaction_id = %s',
                       (2, transaction_id))

        mysql.connection.commit()

        return jsonify({"message": f"Transaction {transaction_id} updated successfully"}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500


# ============================
# 删除交易   参数传递的方式  <string:transaction_id>  <int:transaction_id>  前面是类型 后面是名称
# ============================
@login_app.route('/transactions/<string:transaction_id>', methods=['DELETE'])
def delete_transaction(transaction_id):
    """
    根据 transaction_id 删除交易
    """
    print(transaction_id)
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        sql = "DELETE FROM transactions WHERE transaction_id = %s"
        cursor.execute(sql, (transaction_id,))
        mysql.connection.commit()
        cursor.close()
        return jsonify({"message": f"Transaction {transaction_id} deleted successfully"}), 200
    except Exception as e:
        return jsonify({"error": str(e)}), 500











## 数据分析
@login_app.route('/api/stats', methods=['GET'])
def get_statistics():
    """
    计算统计信息：
    - 总用户数
    - 欺诈交易金额
    - 欺诈交易数量
    - 总交易数量
    - 总交易金额
    """
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)

        # 获取用户数量
        cursor.execute("SELECT COUNT(DISTINCT user_id) as userCount FROM transactions")
        user_count = cursor.fetchone()['userCount']

        # 获取欺诈交易数量 & 欺诈总金额
        cursor.execute("SELECT COUNT(*) as fraudCount, SUM(transaction_amount) as fraudAmount FROM transactions WHERE fraud_label = 1")
        fraud_data = cursor.fetchone()
        fraud_count = fraud_data['fraudCount']
        fraud_amount = fraud_data['fraudAmount'] if fraud_data['fraudAmount'] else 0

        # 获取总交易数量 & 总交易金额
        cursor.execute("SELECT COUNT(*) as transactionCount, SUM(transaction_amount) as transactionAmount FROM transactions")
        transaction_data = cursor.fetchone()
        transaction_count = transaction_data['transactionCount']
        transaction_amount = transaction_data['transactionAmount']

        cursor.close()

        return jsonify({
            "userCount": user_count,
            "fraudAmount": fraud_amount,
            "fraudCount": fraud_count,
            "transactionCount": transaction_count,
            "transactionAmount": transaction_amount
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500


# ============================
# 获取最近 7 天的欺诈交易趋势
# ============================
@login_app.route('/api/fraud-trend', methods=['GET'])
def get_fraud_trend():
    """
    计算最近 7 天每天的欺诈交易数量
    """
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)

        # 获取最近 7 天的数据
        cursor.execute("""
            SELECT DATE(transaction_timestamp) as date, COUNT(*) as fraudCount 
            FROM transactions 
            WHERE fraud_label = 1 
            AND transaction_timestamp >= NOW() - INTERVAL 7 DAY
            GROUP BY date
            ORDER BY date ASC
        """)

        fraud_trend = cursor.fetchall()
        cursor.close()

        # 生成最近 7 天的数据（如果某一天没有数据，则填充 0）
        date_list = [(datetime.datetime.now() - datetime.timedelta(days=i)).strftime('%Y-%m-%d') for i in range(6, -1, -1)]
        fraud_dict = {row['date']: row['fraudCount'] for row in fraud_trend}
        fraud_data = [fraud_dict.get(date, 0) for date in date_list]

        return jsonify({
            "dates": ['周一', '周二', '周三', '周四', '周五', '周六', '周日'],
            "fraudCounts": fraud_data
        }), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500


# ============================
# 获取年龄区间分布
# ============================
@login_app.route('/api/age-distribution', methods=['GET'])
def get_age_distribution():
    """
    计算用户年龄区间分布 (基于 card_age 估算)
    """
    try:
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)

        cursor.execute("""
            SELECT 
                SUM(CASE WHEN card_age BETWEEN 1 AND 24 THEN 1 ELSE 0 END) AS '18-25岁',
                SUM(CASE WHEN card_age BETWEEN 25 AND 60 THEN 1 ELSE 0 END) AS '26-35岁',
                SUM(CASE WHEN card_age BETWEEN 61 AND 120 THEN 1 ELSE 0 END) AS '36-45岁',
                SUM(CASE WHEN card_age BETWEEN 121 AND 180 THEN 1 ELSE 0 END) AS '46-55岁',
                SUM(CASE WHEN card_age > 180 THEN 1 ELSE 0 END) AS '56岁及以上'
            FROM transactions
        """)

        age_data = cursor.fetchone()
        cursor.close()

        return jsonify([
            {"name": "18-25岁", "value": age_data["18-25岁"]},
            {"name": "26-35岁", "value": age_data["26-35岁"]},
            {"name": "36-45岁", "value": age_data["36-45岁"]},
            {"name": "46-55岁", "value": age_data["46-55岁"]},
            {"name": "56岁及以上", "value": age_data["56岁及以上"]}
        ]), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500


# 假设在原有的 login_app 里或者你可以自定义一个 Blueprint
# charts_app = Blueprint('charts_app', __name__)

@login_app.route('/get_charts_data', methods=['GET'])
def get_charts_data():
    """
    返回前端所需的所有可视化数据。
    """
    conn = mysql.connection
    cursor = conn.cursor(MySQLdb.cursors.DictCursor)

    # 1) 近7天交易金额折线图
    # --------------------------------------------------------
    # 以最近7天（含今天）为例，统计每天的交易总金额
    cursor.execute("""
        SELECT 
            DATE(transaction_timestamp) AS date,
            SUM(transaction_amount) AS total_amount
        FROM transactions
        WHERE transaction_timestamp >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
        GROUP BY DATE(transaction_timestamp)
        ORDER BY date
    """)
    last_7d_amount_data = cursor.fetchall()  # List[Dict]

    # 2) 交易类型（欺诈的）的扇形图
    # --------------------------------------------------------
    # 只统计 fraud_label = 1（表示欺诈）的交易，分交易类型聚合
    cursor.execute("""
        SELECT 
            transaction_type,
            COUNT(*) AS fraud_count
        FROM transactions
        WHERE fraud_label = 1
        GROUP BY transaction_type
    """)
    fraud_by_type_data = cursor.fetchall()

    # 3) 地理位置（欺诈的）的扇形图
    # --------------------------------------------------------
    cursor.execute("""
        SELECT 
            location,
            COUNT(*) AS fraud_count
        FROM transactions
        WHERE fraud_label = 1
        GROUP BY location
    """)
    fraud_by_location_data = cursor.fetchall()

    # 4) 商户类型（欺诈的）柱形图
    # --------------------------------------------------------
    cursor.execute("""
        SELECT 
            merchant_category,
            COUNT(*) AS fraud_count
        FROM transactions
        WHERE fraud_label = 1
        GROUP BY merchant_category
    """)
    fraud_by_merchant_data = cursor.fetchall()

    # 5) 是否IP地址标识的柱形图
    # --------------------------------------------------------
    # ip_address_flag = 0 或 1
    cursor.execute("""
        SELECT 
            ip_address_flag,
            COUNT(*) AS total_count
        FROM transactions
        GROUP BY ip_address_flag
    """)
    ip_address_flag_data = cursor.fetchall()

    # 6) 近7日诈骗和未诈骗数量的折线图
    # --------------------------------------------------------
    cursor.execute("""
        SELECT 
            DATE(transaction_timestamp) AS date,
            SUM(CASE WHEN fraud_label = 1 THEN 1 ELSE 0 END) AS fraud_count,
            SUM(CASE WHEN fraud_label = 0 THEN 1 ELSE 0 END) AS not_fraud_count
        FROM transactions
        WHERE transaction_timestamp >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
        GROUP BY DATE(transaction_timestamp)
        ORDER BY date
    """)
    fraud_vs_notfraud_7d_data = cursor.fetchall()

    # 7) 交易距离的扇形图
    # --------------------------------------------------------
    # 可以将交易距离进行区间分类，以便在扇形图中展示
    cursor.execute("""
        SELECT 
            CASE 
                WHEN transaction_distance < 1 THEN '0-1'
                WHEN transaction_distance < 5 THEN '1-5'
                WHEN transaction_distance < 10 THEN '5-10'
                WHEN transaction_distance < 50 THEN '10-50'
                WHEN transaction_distance < 100 THEN '50-100'
                ELSE '>100'
            END AS distance_range,
            COUNT(*) AS cnt
        FROM transactions
        GROUP BY distance_range
        ORDER BY FIELD(distance_range, '0-1','1-5','5-10','10-50','50-100','>100')
    """)
    distance_pie_data = cursor.fetchall()

    # 8) 各个有数值的字段，所有用户加起来的总和
    # --------------------------------------------------------
    cursor.execute("""
        SELECT
            SUM(transaction_amount) AS total_transaction_amount,
            SUM(account_balance) AS total_account_balance,
            SUM(previous_fraudulent_activity) AS total_previous_fraudulent_activity,
            SUM(daily_transaction_count) AS total_daily_transaction_count,
            SUM(avg_transaction_amount_7d) AS total_avg_transaction_amount_7d,
            SUM(failed_transaction_count_7d) AS total_failed_transaction_count_7d,
            SUM(card_age) AS total_card_age,
            SUM(transaction_distance) AS total_transaction_distance,
            SUM(risk_score) AS total_risk_score
        FROM transactions
    """)
    numeric_sums = cursor.fetchone()

    # 处理完后，把所有的数据打包成一个 JSON 返回
    result = {
        "last_7d_amount_data": last_7d_amount_data,
        "fraud_by_type_data": fraud_by_type_data,
        "fraud_by_location_data": fraud_by_location_data,
        "fraud_by_merchant_data": fraud_by_merchant_data,
        "ip_address_flag_data": ip_address_flag_data,
        "fraud_vs_notfraud_7d_data": fraud_vs_notfraud_7d_data,
        "distance_pie_data": distance_pie_data,
        "numeric_sums": numeric_sums
    }

    return jsonify(result)